import pymysql
from datetime import datetime, timedelta

connection = None  # 初始化 connection 变量
# 数据库连接信息
db_config = {
    'host': 'sh-cynosdbmysql-grp-dhfnikj6.sql.tencentcdb.com',
    'port': 21044,
    'user': 'root',
    'password': 'Moregold!23',
    'database': 'nexus',
}

# 连接到数据库
try:
    connection = pymysql.connect(**db_config)
    cursor = connection.cursor()

    # 执行 SQL 查询
    sql_query = """
        SELECT
            oc.created_at,
            oc.checked_at,
            oc.completed_at
        FROM
            order_cards oc,
            orders o
        where
            buyer_company_id = '94'
        AND
            oc.order_id = o.id
        AND
            o.owner_type = 'AdminUser';
            
    """
    cursor.execute(sql_query)
    rows = cursor.fetchall()
    sql1 = """
            SELECT
            oc.created_at,
            oc.checked_at,
            oc.completed_at
        FROM
            order_cards oc,
            orders o
        where
            buyer_company_id = '94'
        AND
            oc.order_id = o.id
        AND
            o.owner_type = 'AdminUser'
        AND
            oc.is_fast='1';
    """
    cursor.execute(sql1)
    rows1 = cursor.fetchall()
    sql0 = """
            SELECT
            oc.created_at,
            oc.checked_at,
            oc.completed_at
        FROM
            order_cards oc,
            orders o
        where
            buyer_company_id = '94'
        AND
            oc.order_id = o.id
        AND
            o.owner_type = 'AdminUser'
        AND
            oc.is_fast='0';
    """
    cursor.execute(sql0)
    rows0 = cursor.fetchall()
    # 初始化变量用于计算平均值
    # 有效数据行数
    total_valid_rows1 = 0
    total_valid_rows2 = 0
    total_valid_rows0 = 0
    total_view_time = 0
    total_operation_time1 = 0
    total_operation_time0 = 0

    # 循环处理每一行数据
    for row in rows:
        created_at, checked_at, completed_at = row
        if checked_at and completed_at and created_at:
            view_time = checked_at - created_at
            operation_time = completed_at - checked_at

            # 检查查看耗时和操作耗时是否小于等于360秒
            if view_time <= timedelta(seconds=360):
                total_valid_rows1 += 1
                # 总查看时间
                total_view_time += view_time.total_seconds()

    # 计算平均查看耗
    avg_view_time = total_view_time / total_valid_rows1
    print(f"查看耗时{avg_view_time}")

    # 循环处理每一行数据
    for row in rows1:
        created_at, checked_at, completed_at = row
        if checked_at and completed_at and created_at:
            operation_time = completed_at - checked_at
            if operation_time <= timedelta(seconds=360):
                total_valid_rows2 += 1
                # 总操作时间
                total_operation_time1 += operation_time.total_seconds()

    # 计算快卡操作耗时
    avg_operation_time1 = total_operation_time1 / total_valid_rows2
    print(f"快卡操作耗时: {avg_operation_time1} 秒")

    # 循环处理每一行数据
    for row in rows0:
        created_at, checked_at, completed_at = row
        if checked_at and completed_at and created_at:
            operation_time = completed_at - checked_at
            total_valid_rows0 += 1
            # 总操作时间
            total_operation_time0 += operation_time.total_seconds()
    # 计算快卡操作耗时
    avg_operation_time0 = total_operation_time0 / total_valid_rows0
    print(f"慢卡操作耗时: {avg_operation_time0} 秒")



except Exception as e:
    print(f"发生错误: {e}")
finally:
    if connection:
        connection.close()
